package datamodule;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import util.FData;
import util.FString;
import conexao.Connect;

public class AlunoDAO {


	private DBuserVO userVO;

	public AlunoDAO( DBuserVO userVO){
		this.userVO = userVO;
	}
	//Inserir Aluno
	public int inserir(AlunoVO VOalu) throws ClassNotFoundException, SQLException {
		Connection con = Connect.getConnection(userVO);
		PreparedStatement pstmtInsert = con.prepareStatement(
				"INSERT INTO aluno(alu_Cod_Alu, alu_Descricao, igr_Cod_Igreja, alu_Endereco, " +
				" cid_Cod_Cidade, alu_UF, alu_CEP, alu_Fone, alu_Celular, alu_E_Mail, " +
				" alu_Sexo, alu_DT_Nasc, cla_Cod_Cla, tur_Cod_Tur, alu_Pai, alu_Mae, alu_Est_Civil, alu_Ativo) "+ 
				" VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ");
		pstmtInsert.setString(1, VOalu.getAlu_Cod_Alu());			
		pstmtInsert.setString(2, VOalu.getAlu_Descricao());
		pstmtInsert.setString(3, VOalu.getIgr_Cod_Igreja());
		pstmtInsert.setString(4, VOalu.getAlu_Endereco());
		pstmtInsert.setString(5, VOalu.getCid_Cod_Cidade());
		pstmtInsert.setString(6, VOalu.getAlu_UF());
		pstmtInsert.setString(7, VOalu.getAlu_CEP());
		pstmtInsert.setString(8, VOalu.getAlu_Fone());
		pstmtInsert.setString(9, VOalu.getAlu_Celular());
		pstmtInsert.setString(10,VOalu.getAlu_E_Mail());
		pstmtInsert.setString(11,VOalu.getAlu_Sexo());	
		pstmtInsert.setString(12,VOalu.getAlu_DT_Nasc() == FData.ignorarD || VOalu.getAlu_DT_Nasc() == null? FData.dataDefaultSQL(): VOalu.getAlu_DT_Nasc_SQL());
		pstmtInsert.setString(13,VOalu.getCla_Cod_Cla());
		pstmtInsert.setString(14,VOalu.getTur_Cod_Tur());
		pstmtInsert.setString(15,VOalu.getAlu_Pai());
		pstmtInsert.setString(16,VOalu.getAlu_Mae());
		pstmtInsert.setString(17,VOalu.getAlu_Est_Civil());
		pstmtInsert.setString(18,VOalu.getAlu_Ativo());
		int reg = pstmtInsert.executeUpdate();	
		con.close();
		return reg;
	}

	//Alterar Aluno
	public int alterar(AlunoVO VOalu) throws ClassNotFoundException, SQLException {
		Connection con = Connect.getConnection(userVO);
		String virgula = "";
		StringBuffer sql = new StringBuffer(" UPDATE aluno SET ");

		if (!VOalu.getAlu_Cod_Alu().equals(FString.ignorar)) {
			sql.append(virgula+" alu_Cod_Alu = '"+VOalu.getAlu_Cod_Alu()+"' ");
			virgula = ",";
		}
		
		if (!VOalu.getAlu_Descricao().equals(FString.ignorar)) {
			sql.append(virgula+" alu_Descricao = '"+VOalu.getAlu_Descricao()+"' ");
			virgula = ",";
		}
		
		if (!VOalu.getIgr_Cod_Igreja().equals(FString.ignorar)) {
			sql.append(virgula+" igr_Cod_Igreja = '"+VOalu.getIgr_Cod_Igreja()+"' ");
			virgula = ",";
		}
				
		if (!VOalu.getAlu_Endereco().equals(FString.ignorar)) {
			sql.append(virgula+" alu_Endereco = '"+VOalu.getAlu_Endereco()+"' ");
			virgula = ",";
		}
		if (!VOalu.getCid_Cod_Cidade().equals(FString.ignorar)) {
			sql.append(virgula+" cid_Cod_Cidade = '"+VOalu.getCid_Cod_Cidade()+"' ");
			virgula = ",";
		}
		if (!VOalu.getAlu_UF().equals(FString.ignorar)) {
			sql.append(virgula+" alu_UF = '"+VOalu.getAlu_UF()+"' ");
			virgula = ",";
		}
		if (!VOalu.getAlu_CEP().equals(FString.ignorar)) {
			sql.append(virgula+" alu_CEP = '"+VOalu.getAlu_CEP()+"' ");
			virgula = ",";
		}
		if (!VOalu.getAlu_Fone().equals(FString.ignorar)) {
			sql.append(virgula+" alu_Fone = '"+VOalu.getAlu_Fone()+"' ");
			virgula = ",";
		}	
		if (!VOalu.getAlu_Celular().equals(FString.ignorar)) {
			sql.append(virgula+" alu_Celular = '"+VOalu.getAlu_Celular()+"' ");
			virgula = ",";
		}
		if (!VOalu.getAlu_E_Mail().equals(FString.ignorar)) {
			sql.append(virgula+" alu_E_Mail = '"+VOalu.getAlu_E_Mail()+"' ");
			virgula = ",";
		}
		if (!VOalu.getAlu_Sexo().equals(FString.ignorar)) {
			sql.append(virgula+" alu_Sexo = '"+VOalu.getAlu_Sexo()+"' ");
			virgula = ",";
		}
		if (VOalu.getAlu_DT_Nasc() != FData.ignorarD) {
			sql.append(virgula+" alu_DT_Nasc = '"+(VOalu.getAlu_DT_Nasc() == FData.ignorarD || VOalu.getAlu_DT_Nasc() == null? FData.dataDefaultSQL(): VOalu.getAlu_DT_Nasc_SQL())+"' ");
			virgula = ",";
		}
		if (!VOalu.getCla_Cod_Cla().equals(FString.ignorar)) {
			sql.append(virgula+" cla_Cod_Cla = '"+VOalu.getCla_Cod_Cla()+"' ");
			virgula = ",";
		}
		if (!VOalu.getTur_Cod_Tur().equals(FString.ignorar)) {
			sql.append(virgula+" tur_Cod_Tur = '"+VOalu.getTur_Cod_Tur()+"' ");
			virgula = ",";
		}
		if (!VOalu.getAlu_Pai().equals(FString.ignorar)) {
			sql.append(virgula+" alu_Pai = '"+VOalu.getAlu_Pai()+"' ");
			virgula = ",";
		}	
		if (!VOalu.getAlu_Mae().equals(FString.ignorar)) {
			sql.append(virgula+" alu_Mae = '"+VOalu.getAlu_Mae()+"' ");
			virgula = ",";
		}
		if (!VOalu.getAlu_Est_Civil().equals(FString.ignorar)) {
			sql.append(virgula+" alu_Est_Civil = '"+VOalu.getAlu_Est_Civil()+"' ");
			virgula = ",";
		}
    	if (!VOalu.getAlu_Ativo().equals(FString.ignorar)) {
		sql.append(virgula+" alu_Ativo = '"+VOalu.getAlu_Ativo()+"' ");
		virgula = ",";
    	}
		
		sql.append(" WHERE alu_Cod_Alu = '"+VOalu.getAlu_Cod_Alu()+"' ");
		PreparedStatement pstmt = con.prepareStatement(sql.substring(0, sql.length()));
		//System.out.println(pstmt);
		int registrosAfetados = pstmt.executeUpdate();
		con.close();
		return registrosAfetados;
	}	
	
	// Deletar Aluno
	public int deletar(AlunoVO VOalu) throws ClassNotFoundException, SQLException {		
		Connection con = Connect.getConnection(userVO);
		PreparedStatement pstmt = con.prepareStatement(
		"DELETE FROM aluno WHERE alu_Cod_Alu = ?");
		pstmt.setString(1, VOalu.getAlu_Cod_Alu());
		int registrosAfetados = pstmt.executeUpdate();
		con.close();
		return registrosAfetados;
	}

	//Pesquisar Aluno
	public AlunoVO pesquisar(AlunoVO VOalu) throws ClassNotFoundException, SQLException {				
		Connection con = Connect.getConnection(userVO);
		StringBuffer sql = new StringBuffer("SELECT * FROM aluno  Where alu_Cod_Alu <> '' ");
		
		if (!VOalu.getAlu_Cod_Alu().equals(FString.ignorar)) {
			sql.append(" AND UPPER(alu_Cod_Alu)= '"+VOalu.getAlu_Cod_Alu()+"' ");			
		}		
		if (!VOalu.getAlu_Descricao().equals(FString.ignorar)) {
			sql.append(" AND UPPER(alu_Descricao) LIKE UPPER('"+VOalu.getAlu_Descricao()+"%"+"')"); 			
		}
		if (!VOalu.getIgr_Cod_Igreja().equals(FString.ignorar)) {
			sql.append(" AND UPPER(igr_Cod_Igreja) = '"+VOalu.getIgr_Cod_Igreja()+"' ");			
		}
	
		//System.out.println(sql);
		PreparedStatement pstmt = con.prepareStatement(sql.substring(0,sql.length()));		
		ResultSet rs = pstmt.executeQuery();
		if (rs.next()) {

			VOalu = new AlunoVO();
			VOalu.setAlu_Cod_Alu   ( rs.getString("alu_Cod_Alu"));
			VOalu.setAlu_Descricao ( rs.getString("alu_Descricao"));
			VOalu.setIgr_Cod_Igreja( rs.getString("igr_Cod_Igreja"));
			VOalu.setAlu_Endereco  ( rs.getString("alu_Endereco"));
			VOalu.setCid_Cod_Cidade( rs.getString("cid_Cod_Cidade"));
			VOalu.setAlu_UF        ( rs.getString("alu_UF"));
			VOalu.setAlu_CEP       ( rs.getString("alu_CEP"));
			VOalu.setAlu_Fone      ( rs.getString("alu_Fone"));
			VOalu.setAlu_Celular   ( rs.getString("alu_Celular"));
			VOalu.setAlu_E_Mail    ( rs.getString("alu_E_Mail"));
			VOalu.setAlu_Sexo      ( rs.getString("alu_Sexo"));
			VOalu.setAlu_DT_Nasc   ( rs.getDate  ("alu_DT_Nasc"));
			VOalu.setCla_Cod_Cla   ( rs.getString("cla_Cod_Cla"));
			VOalu.setTur_Cod_Tur   ( rs.getString("tur_Cod_Tur"));
			VOalu.setAlu_Pai       ( rs.getString("alu_Pai"));
			VOalu.setAlu_Mae       ( rs.getString("alu_Mae"));
			VOalu.setAlu_Est_Civil ( rs.getString("alu_Est_Civil"));
			VOalu.setAlu_Ativo     ( rs.getString("alu_Ativo"));

		}else {
			VOalu = null;
		}
		pstmt.close();
		rs.close();
		con.close();
		return VOalu;
	}

	public ArrayList<AlunoVO> geral(AlunoVO VOalu) throws SQLException, ClassNotFoundException {		
		ArrayList<AlunoVO> lista = null;
		Connection con = Connect.getConnection(userVO);
		StringBuffer sql = new StringBuffer("SELECT * FROM aluno Where alu_Cod_Alu <> ''  ");
		if (!VOalu.getAlu_Cod_Alu().equals(FString.ignorar)) {
			sql.append(" AND UPPER(alu_Cod_Alu) = ('"+VOalu.getAlu_Cod_Alu()+"')");
		}
		if (!VOalu.getAlu_Descricao().equals(FString.ignorar)) {
			sql.append(" AND UPPER(alu_Descricao) = ('"+VOalu.getAlu_Descricao()+"')");
		}
		if (!VOalu.getIgr_Cod_Igreja().equals(FString.ignorar)) {
			sql.append(" AND UPPER(igr_Cod_Igreja) = ('"+VOalu.getIgr_Cod_Igreja()+"')");
		}
		if (!VOalu.getOrdem().equals(FString.ignorar) && !VOalu.getOrdem().isEmpty()) {
			sql.append(" ORDER BY "+VOalu.getOrdem());
		}
		if (!VOalu.getLimit().equals(FString.ignorar) && !VOalu.getLimit().isEmpty()) {
			sql.append(" LIMIT "+VOalu.getLimit());
		}
		System.out.println(sql.toString());
		PreparedStatement pstmt = con.prepareStatement(sql.substring(0,sql.length()));		
		ResultSet rs = pstmt.executeQuery();
		if (rs.next()) {
			lista = new ArrayList<AlunoVO>();
			do {
				AlunoVO alu = new AlunoVO();
				alu.setAlu_Cod_Alu   ( rs.getString("alu_Cod_Alu"));
				alu.setAlu_Descricao ( rs.getString("alu_Descricao"));
				alu.setIgr_Cod_Igreja( rs.getString("igr_Cod_Igreja"));
				alu.setAlu_Endereco  ( rs.getString("alu_Endereco"));
				alu.setCid_Cod_Cidade( rs.getString("cid_Cod_Cidade"));
				alu.setAlu_UF        ( rs.getString("alu_UF"));
				alu.setAlu_CEP       ( rs.getString("alu_CEP"));
				alu.setAlu_Fone      ( rs.getString("alu_Fone"));
				alu.setAlu_Celular   ( rs.getString("alu_Celular"));
				alu.setAlu_E_Mail    ( rs.getString("alu_E_Mail"));
				alu.setAlu_Sexo      ( rs.getString("alu_Sexo"));
				alu.setAlu_DT_Nasc   ( rs.getDate  ("alu_DT_Nasc"));
				alu.setCla_Cod_Cla   ( rs.getString("cla_Cod_Cla"));
				alu.setTur_Cod_Tur   ( rs.getString("tur_Cod_Tur"));
				alu.setAlu_Pai       ( rs.getString("alu_Pai"));
				alu.setAlu_Mae       ( rs.getString("alu_Mae"));
				alu.setAlu_Est_Civil ( rs.getString("alu_Est_Civil"));
				alu.setAlu_Ativo     ( rs.getString("alu_Ativo"));

				lista.add(alu);
			}
			while (rs.next());			
		}
		pstmt.close();
		rs.close();
		con.close();
		return lista;
	}
	
	public ResultSet listagem(AlunoVO VOalu, Connection con) throws SQLException, ClassNotFoundException {		
		StringBuffer sql = new StringBuffer("SELECT "+
		" aluno.`alu_Cod_Alu` AS aluno_alu_Cod_Alu, "+
		" aluno.`alu_Descricao` AS aluno_alu_Descricao, "+
		" aluno.`alu_Endereco` AS aluno_alu_Endereco, "+
		" aluno.`cid_Cod_Cidade` AS aluno_cid_Cod_Cidade, "+
		" aluno.`alu_UF` AS aluno_alu_UF, "+
		" aluno.`alu_CEP` AS aluno_alu_CEP, "+
		" aluno.`alu_Fone` AS aluno_alu_Fone, "+
		" aluno.`alu_Celular` AS aluno_alu_Celular, "+
		" aluno.`alu_E_Mail` AS aluno_alu_E_Mail, "+
		" aluno.`alu_Sexo` AS aluno_alu_Sexo, "+
		" aluno.`alu_DT_Nasc` AS aluno_alu_DT_Nasc, "+
		" aluno.`alu_Pai` AS aluno_alu_Pai, "+
		" aluno.`alu_Mae` AS aluno_alu_Mae, "+
		" aluno.`alu_Est_Civil` AS aluno_alu_Est_Civil, "+
		" aluno.`tur_Cod_Tur` AS aluno_tur_Cod_Tur, "+
		" aluno.`cla_Cod_Cla` AS aluno_cla_Cod_Cla, "+
		" aluno.`igr_Cod_Igreja` AS aluno_igr_Cod_Igreja, "+
		" aluno.`alu_Ativo` AS aluno_alu_Ativo, "+
		" cidades.`cid_Descricao` AS cidades_cid_Descricao, "+
		" turma.`tur_Descricao` AS turma_tur_Descricao, "+
		" classe.`cla_Descricao` AS classe_cla_Descricao, "+
		" igrejas.`igr_Descricao` AS igrejas_igr_Descricao "+
		" FROM   `cidades` cidades INNER JOIN `aluno` aluno ON cidades.`cid_Cod_Cidade` = aluno.`cid_Cod_Cidade` "+
		"	     INNER JOIN `turma` turma ON aluno.`tur_Cod_Tur` = turma.`tur_Cod_Tur` "+
		"	     INNER JOIN `classe` classe ON aluno.`cla_Cod_Cla` = classe.`cla_Cod_Cla` "+
		"	     INNER JOIN `igrejas` igrejas ON aluno.`igr_Cod_Igreja` = igrejas.`igr_Cod_Igreja` "+
		" Where aluno.`alu_Cod_Alu` <> '' ");
				
		if (!VOalu.getAlu_Cod_Alu().equals(FString.ignorar) && !VOalu.getAlu_Cod_Alu().isEmpty()) {
			sql.append(" AND UPPER(aluno.`alu_Cod_Alu`) = '"+VOalu.getAlu_Cod_Alu()+"' ");			
		}		
		if (!VOalu.getAlu_Descricao().equals(FString.ignorar) && !VOalu.getAlu_Descricao().isEmpty()) {
			sql.append(" AND UPPER(aluno.`alu_Descricao`) LIKE UPPER('"+VOalu.getAlu_Descricao()+"%"+"')"); 			
		}

		if (!VOalu.getIgr_Cod_Igreja().equals(FString.ignorar) && !VOalu.getIgr_Cod_Igreja().isEmpty()) {
			sql.append(" AND UPPER(aluno.`igr_Cod_Igreja`) = '"+VOalu.getIgr_Cod_Igreja()+"' ");
		}


		if (!VOalu.getAlu_UF().equals(FString.ignorar) && !VOalu.getAlu_UF().isEmpty()) {
			sql.append(" AND UPPER(aluno.`alu_UF`) = ('"+VOalu.getAlu_UF()+"')");
		}

		if (!VOalu.getCid_Cod_Cidade().equals(FString.ignorar) && !VOalu.getCid_Cod_Cidade().isEmpty()) {
			sql.append(" AND UPPER(aluno.`cid_Cod_Cidade`) = ('"+VOalu.getCid_Cod_Cidade()+"')");
		}
		
		
		if (!VOalu.getOrdem().equals(FString.ignorar) && !VOalu.getOrdem().isEmpty()) {
			sql.append(" ORDER BY "+VOalu.getOrdem());
		}
		if (!VOalu.getLimit().equals(FString.ignorar)) {
			sql.append(" LIMIT "+VOalu.getLimit());
		}
		PreparedStatement pstmt = con.prepareStatement(sql.substring(0,sql.length()));
		System.out.println(pstmt);
		ResultSet rs = pstmt.executeQuery();
		return rs;
	}	
	
}